
from pymysql import Connection


def getConn(host: str, port: int, user: str, password: str) -> Connection:
    con = Connection(
        host=host,
        port=port,
        user=user,
        password=password,
        autocommit=True
    )
    return con


def getFileDetails(addr: str, mode: str):
    with  open(addr, mode, encoding="UTF-8") as file:
        title = file.readline()
        details = file.readlines()
        return title, details


def createTbale(con: Connection, dbName: str, tableName: str):
    ##选择数据库
    con.select_db(dbName)
    # 创建游标
    cu = con.cursor()
    cu.execute(f"DROP TABLE IF EXISTS {tableName};")
    execNum = cu.execute(
        f"CREATE TABLE {tableName} (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20),date VARCHAR(20), money DECIMAL(10, 2), type VARCHAR(20), remarks VARCHAR(20));")
    print(f"创建表成功{tableName}")


def addInfoTotable(con: Connection, dbName: str, tableName: str, details):
    ##选择数据库
    con.select_db(dbName)
    # 创建游标
    cu = con.cursor()

    for data in details:
        dList = data.strip("\n").split(",")
        sqlStr = f"INSERT INTO {tableName} (name, date,money,type,remarks) VALUES ('{dList[0]}','{dList[1]}',{dList[2]},'{dList[3]}','{dList[4]}');"
        cu.execute(sqlStr)
        print(f"数据插入成功")


def readTableInfo(con: Connection, dbName: str, tableName: str):
    ##选择数据库
    con.select_db(dbName)
    # 创建游标
    cu = con.cursor()

    selectSql = f"select * from {tableName}"
    cu.execute(selectSql)
    return cu.fetchall()

def writeFile(fileAddr,mode,content:str):
    with open(fileAddr,mode,encoding="UTF-8") as file:
        file.write(content)

hostVal: str = "127.0.0.1"
portVal = 3306
userVal = "root"
passwordVal = "jzws0927"

try:
    title, details = getFileDetails("D:/bill.txt", "r")
    conn = getConn(hostVal, portVal, userVal, passwordVal)
    # createTbale(conn, "cms", "bill")
    # addInfoTotable(conn, "cms", "bill", details)
    billInfo = readTableInfo(conn, "cms", "bill")


    for item in billInfo:
        writeFile("D:/billCopy.txt", "a", str(item)+"\n")

    conn.close()

except Exception as ex:
    print(ex)
